﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.OleDb;

public partial class add_guest : System.Web.UI.Page
{
    OleDbConnection conx;

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            String con = "Provider=MSDAORA;Data Source=orcl;Password=oracle;User ID=pearl";//"DSN=orcl;Driver={Microsoft ODBC for Oracle};UID=system;PWD=password;SERVER=localhost;";
            conx = new OleDbConnection(con);
            conx.Open();

            if (conx.State == ConnectionState.Open)
            {
                Label1.Text = "";

                DataSet ds = new DataSet("Dataset1");

                OleDbDataAdapter ODDA = new OleDbDataAdapter("SELECT AMOUNT FROM COUNTS WHERE NAME = 'GUEST'", conx);
                ODDA.Fill(ds, "MYDATA");

                string test = ds.Tables["MYDATA"].Rows[0]["AMOUNT"].ToString();
                //Label2.Text = test;
                int guest_amt = Convert.ToInt32(test);

                string guest_id = "";
                if (guest_amt < 10)
                {
                    guest_id = "G000" + guest_amt;
                    Label3.Text = guest_id;
                }
                else if (guest_amt >= 10 && guest_amt <= 99)
                {
                    guest_id = "G00" + guest_amt;
                    Label3.Text = guest_id;
                }
                else if (guest_amt >= 100 && guest_amt <= 999)
                {
                    guest_id = "G0" + guest_amt;
                    Label3.Text = guest_id;
                }
                else
                {
                    guest_id = "G" + guest_amt;
                    Label3.Text = guest_id;
                
                }

            }
            else
                Label1.Text = "Oracle Connect Failed";

        }

        catch (Exception ex)
        {
            Label1.Text = ex.Message + "<br />" + ex.StackTrace;
        }

        finally
        {
            conx.Close();
            conx = null;
        }
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox4.Text = "";
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        Response.Redirect("guest.aspx");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            String con = "Provider=MSDAORA;Data Source=orcl;Password=oracle;User ID=pearl";//"DSN=orcl;Driver={Microsoft ODBC for Oracle};UID=system;PWD=password;SERVER=localhost;";
            conx = new OleDbConnection(con);
            conx.Open();

            if (conx.State == ConnectionState.Open)
            {
                Label1.Text = "Oracle Connected";

                string ddl1 = DropDownList1.SelectedValue;
                //Label4.Text = ddl1;
                string tb1 = TextBox1.Text;
                string tb2 = TextBox2.Text;
                string tb3 = TextBox3.Text;
                string tb4 = TextBox4.Text;
                string ddl2 = DropDownList2.SelectedValue;

                TextBox1.Text = "";
                TextBox2.Text = "";
                TextBox3.Text = "";
                TextBox4.Text = "";

                DataSet ds = new DataSet("Dataset1");

                OleDbDataAdapter ODDA = new OleDbDataAdapter("SELECT AMOUNT FROM COUNTS WHERE NAME = 'GUEST'", conx);
                ODDA.Fill(ds, "MYDATA");

                string test = ds.Tables["MYDATA"].Rows[0]["AMOUNT"].ToString();
                //Label2.Text = test;
                int guest_amt = Convert.ToInt32(test);

                string guest_id = "";
                if (guest_amt < 10)
                {
                    guest_id = "G000" + guest_amt;
                    //Label3.Text = guest_id;
                }
                else if (guest_amt >= 10 && guest_amt <= 99)
                {
                    guest_id = "G00" + guest_amt;
                    //Label3.Text = guest_id;
                }
                else if (guest_amt >= 100 && guest_amt <=999)
                {
                    guest_id = "G0" + guest_amt;
                    //Label3.Text = guest_id;
                }
                else if (guest_amt >= 1000)
                {
                    guest_id = "G0" + guest_amt;
                }

                
                OleDbCommand ODC = new OleDbCommand(
                    "INSERT INTO GUESTS VALUES('" 
                    + guest_id + "'," + ddl1 + ",'" + tb1 + "','" + tb2 + "','" + tb3 + "','" + ddl2 + "','" + tb4 + "')", conx);
                ODC.ExecuteNonQuery();
                OleDbCommand ODC_commit = new OleDbCommand("COMMIT", conx);
                ODC_commit.ExecuteNonQuery();

                // Update Count Table
                guest_amt = guest_amt + 1;
                OleDbCommand ODC_update = new OleDbCommand("UPDATE COUNTS SET AMOUNT = " + guest_amt + " WHERE NAME = 'GUEST'", conx);
                ODC_update.ExecuteNonQuery();

                Label1.Text = "\"New guest Inserted\"";
                //Label1.Text = "\"New Staff Inserted\" Your Staff ID : " + staff_id;
                //OleDbCommand ODC = new OleDbCommand("INSERT INTO PRENAMES VALUES("+tb1+","+tb2+");", conx);
                //ODC.ExecuteNonQuery();
                //Label1.Text = Label1.Text + "Inserted " +ODC.ExecuteNonQuery()+" rows.";

                ODC = null;
            }
            else
                Label1.Text = "Oracle Connect Failed";

        }

        catch (Exception ex)
        {
            Label1.Text = ex.Message + "<br />" + ex.StackTrace;
        }

        finally
        {
            conx.Close();
            conx = null;
        }
    }
}